﻿using System;
using System.Data;
using System.Data.SQLite;
using System.Configuration;

/// <summary></summary>  
/// SQLiteHelper 的摘要说明  
///   
public class SQLHelper
{

    private static string connectionString = ConfigurationSettings.AppSettings["connString"];

    #region 静态私有方法
    /// <summary></summary>  
    /// 附加参数  
    ///   
    /// <param name="command">  
    /// <param name="commandParameters">  
    private static void AttachParameters(SQLiteCommand command, SQLiteParameter[] commandParameters)
    {
        command.Parameters.Clear();
        foreach (SQLiteParameter p in commandParameters)
        {
            if (p.Direction == ParameterDirection.InputOutput && p.Value == null)
                p.Value = DBNull.Value;
            command.Parameters.Add(p);
        }
    }
    /// <summary></summary>  
    /// 分配参数值  
    ///   
    /// <param name="commandParameters">  
    /// <param name="parameterValues">  
    private static void AssignParameterValues(SQLiteParameter[] commandParameters, object[] parameterValues)
    {
        if (commandParameters == null || parameterValues == null)
            return;
        if (commandParameters.Length != parameterValues.Length)
            throw new ArgumentException("Parameter count does not match Parameter Value count.");
        for (int i = 0, j = commandParameters.Length; i < j; i++)
        {
            commandParameters[i].Value = parameterValues[i];
        }
    }
    /// <summary></summary>  
    /// 预备执行command命令  
    ///   
    /// <param name="command">  
    /// <param name="connection">  
    /// <param name="transaction">  
    /// <param name="commandType">  
    /// <param name="commandText">  
    /// <param name="commandParameters">  
    private static void PrepareCommand(SQLiteCommand command,
        SQLiteConnection connection, SQLiteTransaction transaction,
        string commandText, SQLiteParameter[] commandParameters
        )
    {
        //if (commandType == CommandType.StoredProcedure)
        //{
        //    throw new ArgumentException("SQLite 暂时不支持存储过程");
        //}
        if (connection.State != ConnectionState.Open)
            connection.Open();
        command.Connection = connection;
        command.CommandText = commandText;
        if (transaction != null)
            command.Transaction = transaction;
        command.CommandType = CommandType.Text;
        if (commandParameters != null)
            AttachParameters(command, commandParameters);
        return;
    }
    #endregion

    #region ExecuteNonQuery 执行SQL命令，返回影响行数
    /// <summary></summary>  
    /// 执行SQL命名  
    ///   
    /// <param name="connectionString">  
    /// <param name="commandType">  
    /// <param name="commandText">  
    /// <returns></returns>  
    public static int ExecuteNonQuery(string commandText)
    {
        return ExecuteNonQuery(commandText, (SQLiteParameter[])null);
    }
    /// <summary></summary>  
    /// 不支持存储过程，但可以参数化查询  
    ///   
    /// <param name="connectionString">  
    /// <param name="commandType">  
    /// <param name="commandText">  
    /// <param name="commandParameters">  
    /// <returns></returns>  
    public static int ExecuteNonQuery(string commandText, params SQLiteParameter[] commandParameters)
    {
        using (SQLiteConnection conn = new SQLiteConnection(connectionString))
        {

            conn.Open();
            SQLiteCommand cmd = new SQLiteCommand();
            PrepareCommand(cmd, conn, (SQLiteTransaction)null, commandText, commandParameters);
            int retval = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return retval;
        }
    }

    //public static int ExecuteNonQuery(SQLiteConnection connection, string commandText, params SQLiteParameter[] commandParameters)
    //{
    //    SQLiteCommand cmd = new SQLiteCommand();
    //    PrepareCommand(cmd, connection, (SQLiteTransaction)null, commandText, commandParameters);
    //    int retval = cmd.ExecuteNonQuery();
    //    cmd.Parameters.Clear();
    //    return retval;
    //}
    #endregion

    #region ExecuteDataSet 执行SQL查询，并将返回数据填充到DataSet
    //public static DataSet ExecuteDataset(SQLiteConnection connection, string commandText, params SQLiteParameter[] commandParameters)
    //{
    //    SQLiteCommand cmd = new SQLiteCommand();
    //    PrepareCommand(cmd, connection, (SQLiteTransaction)null, commandText, commandParameters);
    //    SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
    //    DataSet ds = new DataSet();
    //    da.Fill(ds);
    //    cmd.Parameters.Clear();
    //    return ds;
    //}
    //public static DataSet ExecuteDataset(SQLiteConnection connection, string commandText)
    //{
    //    return ExecuteDataset(connection, commandText, (SQLiteParameter[])null);
    //}
    public static DataSet ExecuteDataset(string commandText, params SQLiteParameter[] commandParameters)
    {

        using (SQLiteConnection cn = new SQLiteConnection(connectionString))
        {
            cn.Open();
            SQLiteCommand cmd = new SQLiteCommand();
            PrepareCommand(cmd, cn, (SQLiteTransaction)null, commandText, commandParameters);
            SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);
            cmd.Parameters.Clear();
            return ds;
        }
    }

    public static DataSet ExecuteDataset(string commandText)
    {
        return ExecuteDataset(commandText, (SQLiteParameter[])null);
    }
    #endregion

    //#region ExecuteReader 执行SQL查询,返回DbDataReader
    public static SQLiteDataReader ExecuteReader(string commandText, params SQLiteParameter[] commandParameters)
    {
        using (SQLiteConnection conn = new SQLiteConnection(connectionString))
        {
            SQLiteCommand cmd = new SQLiteCommand();
            PrepareCommand(cmd, conn, (SQLiteTransaction)null, commandText, commandParameters);
            SQLiteDataReader dr;
            //if (connectionOwnership == DbConnectionOwnership.External)
            //    dr = cmd.ExecuteReader();
            //else
            dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            cmd.Parameters.Clear();
            return dr;
        }
    }
    /// <summary></summary>  
    ///读取数据后将自动关闭连接  
    ///   
    /// <param name="connectionString">  
    /// <param name="commandType">  
    /// <param name="commandText">  
    /// <param name="commandParameters">  
    /// <returns></returns>  
    public static SQLiteDataReader ExecuteReader(string commandText)
    {
        return ExecuteReader(commandText, (SQLiteParameter[])null);
    }


    /// <summary></summary>  
    /// DbConnectionOwnership DataReader以后是否自动关闭连接  
    ///   
    public enum DbConnectionOwnership
    {
        /// <summary></summary>  
        /// 自动关闭  
        ///   
        Internal,
        /// <summary></summary>  
        /// 手动关闭  
        ///   
        External,
    }
    ///// <summary></summary>  
    ///// 读取数据后将自动关闭连接  
    /////   
    ///// <param name="connectionString">  
    ///// <param name="commandType">  
    ///// <param name="commandText">  
    ///// <returns></returns>  
    //public static SQLiteDataReader ExecuteReader(string commandText)
    //{
    //    return ExecuteReader(connectionString, CommandType.Text, commandText, (SQLiteParameter[])null);
    //}
    ///// <summary></summary>  
    ///// 读取数据以后需要自行关闭连接  
    /////   
    ///// <param name="connection">  
    ///// <param name="commandType">  
    ///// <param name="commandText">  
    ///// <returns></returns>  
    //public static SQLiteDataReader ExecuteReader(SQLiteConnection connection, string commandText)
    //{
    //    return ExecuteReader(connection, CommandType.Text, commandText, (SQLiteParameter[])null);
    //}
    ///// <summary></summary>  
    ///// 读取数据以后需要自行关闭连接  
    /////   
    ///// <param name="connection">  
    ///// <param name="commandType">  
    ///// <param name="commandText">  
    ///// <param name="commandParameters">  
    ///// <returns></returns>  
    //public static SQLiteDataReader ExecuteReader(SQLiteConnection connection, string commandText, params SQLiteParameter[] commandParameters)
    //{
    //    return ExecuteReader(connection, (SQLiteTransaction)null, CommandType.Text, commandText, commandParameters, DbConnectionOwnership.External);
    //}
    //#endregion
}